import MySQLdb
from pymysql.converters import escape_string
from django.http.response import JsonResponse, HttpResponse, HttpResponseBadRequest
from django.views.decorators.http import require_http_methods

from utils import http_response
from utils.ThreadPoolFactory import CustomThreadPool
import pandas as pd
import utils.http_response as hr
import sys
import json

sys.path.insert(0, '..')
from mysite import db_config
from check.autocheck import Check, MyThread


@require_http_methods(['GET'])
def rule(request):
    """
    根据公司名查询所有检核规则详情
    """
    company = request.GET.get('name')
    filter = request.GET.get('risk_market_filter')
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    curs.execute('set autocommit=0')
    try:
        sql = """
        SELECT
        a.id,
        de.name check_item,
        target_table,
        risk_market_item,
        COALESCE ( b.cmc, a.problem_type ) problem_type,
        COALESCE ( c.NAME, a.db ) db,
        check_sql,
        a.note,
        STATUS,
        source_system 
    FROM
        check_result_template a
        LEFT JOIN hbd_aa10 b ON a.problem_type = b.dm and b.category='quality'
        LEFT JOIN source_db_info c ON a.db = c.id 
        LEFT JOIN data_standard_desc de ON de.index_id = a.check_item
    WHERE
        source_system IN ( '{company}' ) 
        AND risk_market_item LIKE '%{filter}%'
        """.format(company=company, filter=filter)
        curs.execute(sql)
        result = curs.fetchall()
        # 构造json
        result_list = []
        for i in result:
            result_dict = {"id": i[0], "check_item": i[1], "target_table": i[2], "risk_market_item": i[3],
                           "problem_type": i[4], "db": i[5], "check_sql": i[6], "note": i[7], "status": i[8],
                           "source_system": i[9]}
            result_list.append(result_dict)
        json_data = {'data': result_list}
        return JsonResponse(json_data)
    except Exception as e:
        return HttpResponse(str(e), status=500)
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def rule_detail(request):
    """
    根据公司名、id查询单条规则详情
    """
    company = request.GET.get('company')
    id = request.GET.get('id')

    data = {
        "id": None,
        "source_system": None,
        "check_item": None,
        "target_table": None,
        "risk_market_item": None,
        "problem_type": None,
        "db": None,
        "check_sql": None,
        "note": None,
        "status": None,
    }
    if id == 'null':
        return JsonResponse(data)

    sql = f"""select a.id,de.name check_item,target_table,risk_market_item,problem_type,db,check_sql,note,status
    from check_result_template a
    left join data_standard_desc de on de.index_id = a.check_item
    where a.source_system in ('{company}') and a.id={id}"""
    conn = db_config.sqlalchemy_conn()
    try:
        result = pd.read_sql(sql, con=conn)
        data = {
            "check_item": result['check_item'].values.tolist()[0],
            "target_table": result['target_table'].values.tolist()[0],
            "risk_market_item": result['risk_market_item'].values.tolist()[0],
            "problem_type": result['problem_type'].values.tolist()[0],
            "db": result['db'].values.tolist()[0],
            "check_sql": result['check_sql'].values.tolist()[0],
            "note": result['note'].values.tolist()[0],
            "status": result['status'].values.tolist()[0],
        }
        # 分母
        detail_sql_denominator = f"""
        select 
         id,category,category_order,pid,t_name,c_name,check_sql,
         effective,denominator_order
from check_result_detail_template  where pid = {id} AND company = '{company}' and type = 'denominator'
        """
        # 分子
        detail_sql_numerator = f"""
        select 
         id,category,category_order,pid,t_name,c_name,check_sql,
         effective,denominator_order
from check_result_detail_template  where pid = {id} AND company = '{company}' and type = 'numerator'
        """
        # 分目sql信息
        cur_conn = conn.raw_connection()
        with cur_conn.cursor() as cursor:
            cursor.execute(detail_sql_denominator)
            result_detail_denominator = cursor.fetchall()
            denominator_list = []
            for detail in result_detail_denominator:
                denominator_dict = {
                    "id": detail[0],
                    "category": detail[1],
                    "category_order": detail[2],
                    "pid": detail[3],
                    "t_name": detail[4],
                    "c_name": detail[5],
                    "check_sql": detail[6],
                    "effective": detail[7],
                    "denominator_order": detail[8]
                }
                denominator_list.append(denominator_dict)
            data["denominator"] = denominator_list
            # 分子sql
            cursor.execute(detail_sql_numerator)
            result_detail_numerator = cursor.fetchall()
            numerator_list = []
            for detail in result_detail_numerator:
                numerator_dict = {
                    "id": detail[0],
                    "category": detail[1],
                    "category_order": detail[2],
                    "pid": detail[3],
                    "t_name": detail[4],
                    "c_name": detail[5],
                    "check_sql": detail[6],
                    "effective": detail[7],
                    "denominator_order": detail[8]
                }
                numerator_list.append(numerator_dict)
            data["numerator"] = numerator_list

        return JsonResponse(hr.repo().success(msg="成功", data=data))
    except Exception as e:
        return JsonResponse(hr.repo().fail(str(e)))
    finally:
        conn.dispose()


@require_http_methods(['POST'])
def rule_update(request):
    """
    新增检核规则
    """
    all_params = request.body
    if all_params is None:
        return JsonResponse(http_response.repo().fail(msg='参数为空'))

    param_dict = json.loads(all_params)

    source_system = param_dict.get('source_system')
    check_item = param_dict.get('check_item')
    target_table = param_dict.get('target_table')
    risk_market = param_dict.get('risk_market')
    problem_type = param_dict.get('problem_type')
    db = param_dict.get('db')
    note = param_dict.get('note')
    id = param_dict.get('id')
    status = param_dict.get('status')
    # 解析分子 sql
    numerator = param_dict.get('numerator')
    denominator = param_dict.get('denominator')
    delete_ids = param_dict.get('delete_ids')

    try:
        # 连接数据库
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        curs.execute('set autocommit=0')
        sql = f"""update check_result_template set 
                                                    source_system = '{source_system}',
                                                    company = '{source_system}',
                                                    check_item = '{check_item}',
                                                    target_table = '{target_table}',
                                                    risk_market_item = '{risk_market}',
                                                    problem_type = '{problem_type}',
                                                    db = '{db}',
                                                    note = '{note}',
                                                    status = '{status}'
                                                    
                                                    where id = {id}
                                                    and company = '{source_system}'
                """
        curs.execute(sql)
        # 插入明细数据 id  sql
        for i in range(len(numerator)):
            sql_dict = numerator[i]
            if sql_dict.get("id") is None:
                exe_add_sql = f"""insert into check_result_detail_template(
            category,pid,t_name,c_name,check_sql,effective,company,denominator_order,type
            )values ('{sql_dict.get('category')}','{id}','{sql_dict.get('t_name')}','{sql_dict.get('c_name')}',
            '{escape_string(sql_dict.get('check_sql'))}',{sql_dict.get('effective')},'{source_system}',{sql_dict.get('denominator_order')},'numerator')
            
            """
                curs.execute(exe_add_sql)
            else:
                exe_sql = f"""update check_result_detail_template set 
            category = '{sql_dict.get('category')}',pid ={id},t_name ='{sql_dict.get('t_name')}',
            c_name = '{sql_dict.get('c_name')}',check_sql = '{escape_string(sql_dict.get('check_sql'))}',
            effective = {sql_dict.get('effective')},company = '{source_system}',
            denominator_order ={sql_dict.get('denominator_order')}
            where id = {sql_dict.get('id')}
            """
                curs.execute(exe_sql)
        for i in range(len(denominator)):
            sql_dict = denominator[i]
            if sql_dict.get("id") is None:
                exe_add_sql = f"""insert into check_result_detail_template(
            category,category_order,pid,t_name,c_name,check_sql,effective,company,type
            )values ('{sql_dict.get('category')}',{i + 1},'{id}','{sql_dict.get('t_name')}','{sql_dict.get('c_name')}',
            '{escape_string(sql_dict.get('check_sql'))}',{sql_dict.get('effective')},'{source_system}','denominator')
            """
                curs.execute(exe_add_sql)
            else:
                exe_sql = f"""update check_result_detail_template set 
            category = '{sql_dict.get('category')}',
            category_order = {i + 1},
            pid ={id},t_name ='{sql_dict.get('t_name')}',
            c_name = '{sql_dict.get('c_name')}',check_sql = '{escape_string(sql_dict.get('check_sql'))}',
            effective = {sql_dict.get('effective')},company = '{source_system}'
            where id = {sql_dict.get('id')}
            """
                curs.execute(exe_sql)
        # 将所有待删除的数据进行删除
        for delete_id in delete_ids:
            delete_sql = f""" delete from check_result_detail_template where id = {delete_id}"""
            curs.execute(delete_sql)

        conn.commit()
        return JsonResponse(hr.repo().success("修改成功"))
    except Exception as e:
        return JsonResponse(hr.repo().fail(str(e)))
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def rule_add(request):
    """
    新增检核规则
    """
    all_params = request.body
    if all_params is None:
        return JsonResponse(http_response.repo().fail(msg='参数为空'))

    param_dict = json.loads(all_params)

    source_system = param_dict.get('source_system')
    check_item = param_dict.get('check_item')
    target_table = param_dict.get('target_table') if param_dict.get('target_table') is not None else ''
    risk_market = param_dict.get('risk_market')
    problem_type = param_dict.get('problem_type')
    db = param_dict.get('db')
    note = param_dict.get('note')
    status = param_dict.get('status')
    # 解析分子 sql
    numerator = param_dict.get('numerator')
    denominator = param_dict.get('denominator')

    try:
        # 连接数据库
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        curs.execute('set autocommit=0')
        sql = "select coalesce(max(id),0)+1 from check_result_template where source_system in ('" + source_system + "')"
        curs.execute(sql)
        result = curs.fetchone()
        new_id = str(result[0])  # 获取新增的id
        sql = f"""insert into check_result_template(id,
                                                    source_system,
                                                    company,
                                                    check_item,
                                                    target_table,
                                                    risk_market_item,
                                                    problem_type,
                                                    note,
                                                    status,
                                                    db)
                values({new_id},
                        '{source_system}',
                        '{source_system}',
                        '{check_item}',
                        '{target_table}',
                        '{risk_market}',
                        '{problem_type}',
                        '{note}',
                        '{status}',
                        '{db}')"""
        # print(sql)
        curs.execute(sql)
        # 插入明细数据 id  sql
        for sql_dict in numerator:
            category = sql_dict.get('category')
            sql = escape_string(sql_dict.get('check_sql'))
            exe_sql = f"""insert into check_result_detail_template(
            category,pid,t_name,c_name,check_sql,effective,company,denominator_order,type
            )values ('{category}','{new_id}','{sql_dict.get('t_name')}','{sql_dict.get('c_name')}',
            '{sql}',{sql_dict.get('effective')},'{source_system}',{sql_dict.get('denominator_order')},'numerator')
            
            """
            curs.execute(exe_sql)

        for i in range(len(denominator)):
            sql_dict = denominator[i]
            category = sql_dict.get('category')
            sql = escape_string(sql_dict.get('check_sql'))
            exe_sql = f"""insert into check_result_detail_template(
            category,category_order,pid,t_name,c_name,check_sql,effective,company,type
            )values ('{category}',{i + 1},'{new_id}','{sql_dict.get('t_name')}','{sql_dict.get('c_name')}',
            '{sql}',{sql_dict.get('effective')},'{source_system}','denominator')
            """
            curs.execute(exe_sql)

        conn.commit()
        return JsonResponse(hr.repo().success("添加成功"))
    except Exception as e:
        return JsonResponse(hr.repo().fail(str(e)))
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def rule_delete(request):
    all_params = request.body
    if all_params is None:
        return JsonResponse(http_response.repo().fail(msg='参数为空'))
    param_dict = json.loads(all_params)
    pid = param_dict.get("id")
    source_system = param_dict.get('source_system')
    p_sql = "delete from check_result_template where id = {pid} and  source_system = '{source_system}'".format(pid=pid,
                                                                                                               source_system=source_system)
    sub_sql = "delete from check_result_detail_template where pid = {pid} and  company = '{company}'".format(pid=pid,
                                                                                                             company=source_system)
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        curs.execute('set autocommit=0')
        curs.execute(sub_sql)
        curs.execute(p_sql)
        conn.commit()
        return JsonResponse(hr.repo().success("删除成功"))
    except Exception as e:
        return JsonResponse(hr.repo().fail(str(e)))
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def rule_sub_update(request):
    sqls = request.POST.get('sqls')
    sql_arr = json.loads(sqls)
    try:
        # 连接数据库
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        curs.execute('set autocommit=0')
        for sql_dict in sql_arr:
            exe_sql = f"""update check_result_detail_template
            set category = '{sql_dict.get('category')}',
            t_name = '{sql_dict.get('t_name')}',
            c_name = '{sql_dict.get('c_name')}',
            check_sql = '{escape_string(sql_dict.get('check_sql'))}',
            effective = {sql_dict.get('effective')},
            company = '{sql_dict.get('company')}'
            where id = sql_dict.get('sql_id')
            """
            curs.execute(exe_sql)
        conn.commit()
        return JsonResponse({'msg': '修改成功', 'code': 1000})
    except Exception as e:
        return HttpResponse(e, status=500)
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def rule_status_modify(request):
    """修改检核规则状态，禁用/启用 规则的状态
    """
    id = request.POST.get('id')
    post_status = request.POST.get('status')
    company = request.POST.get('company')

    conn = db_config.mysql_connect()
    curs = conn.cursor()
    curs.execute('set autocommit=0')
    # 修改状态
    try:
        if post_status == '已启用':
            sql = f"update check_result_template set status='已停用' where id={id} and source_system='{company}'"
            rr = curs.execute(sql)
            conn.commit()
            return JsonResponse({'msg': '修改成功', 'code': 1000})
        else:
            sql = f"update check_result_template set status='已启用' where id={id} and source_system='{company}'"
            rr = curs.execute(sql)
            conn.commit()
            return JsonResponse({'msg': '修改成功', 'code': 1000})
    except Exception as e:
        return HttpResponse(f'{e}', status=500)
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def rule_execute(request):
    """执行检核
       数据核查版本信息由接口控制，表示该次核查的具体版本号，如果版本号不存在，
       核查完插入版本号，否则保持该版本号信息
    """
    params = json.loads(request.body)
    return init_and_submit_job(params)


def init_and_submit_job(params):
    company = params.get('company')
    # username = params.get('username')
    username = "admin"
    # company_db = params.get('db')
    version = params.get('version')
    if company is None:
        return JsonResponse({
            "status": "fail",
            "msg": "必填项存在空值[company]"
        })
    source_system = company
    check = Check(company=company, version=version, username=username)
    if check.init_table():
        obj_list = []
        executor = CustomThreadPool.get_pool()
        # 提交检查任务
        data = executor.submit(check.run_check)
        obj_list.append(data)
    return JsonResponse(hr.repo().success(source_system + "后台任务核检中，请稍后查询结果！"))


def result_info(company, quarter, source_system, username, db):
    conn = db_config.mysql_connect()
    with conn.cursor() as curs:
        curs.execute('set autocommit=0')
        sql = "insert into check_execute_log(status,company,execute_date,execute_user,db) " \
              "values('success','{1}',now(),'{2}','{3}')".format(quarter, company, username, db)
        print(sql)
        curs.execute(sql)
    conn.commit()
    conn.close()
    return JsonResponse({
        "status": "success",
        "msg": source_system + "公司检核成功！"
    })


def execute(request):
    """执行检核
    """
    company = request.GET.get('company')
    username = request.GET.get('username')
    quarter = request.GET.get('quarter')
    company_db = request.GET.get('company_db')
    source_system = company
    if company == 'xt':
        check = Check(company)
        if check.init_table():
            # 初始化3个线程
            thread1 = MyThread(func=check.run_check,
                               args=(company_db,))
            # thread2 = MyThread(func=check.run_check,args=('sqlserver',))
            # thread3 = MyThread(func=check.xt_spec, args=(quarter,))
            # thread4 = MyThread(func=check.run_check,args=('mysql',))
            # 开启3个线程
            thread1.start()
            # thread2.start()
            # thread3.start()
            # thread4.start()
            # 等待运行结束
            # thread1.join()
            # thread2.join()
            # thread3.join()
            # thread4.join()
            run = True
            # if thread1.get_result() is True:
            #     if thread2.get_result() is True:
            #         # if thread3.get_result() is True:
            #             if thread4.get_result() is True:
            #                 run = True
            #             else:
            #                 return JsonResponse({
            #                     "status":
            #                         "检核过程发生错误：" + str(thread4.get_result())
            #                 })
            #         # else:
            #         #     return JsonResponse({
            #         #         "status":
            #         #             "检核过程发生错误：" + str(thread3.get_result())
            #         #     })
            #     else:
            #         return JsonResponse(
            #             {"status": "检核过程发生错误：" + str(thread2.get_result())})
            # else:
            #     return JsonResponse(
            #         {"status": "检核过程发生错误：" + str(thread1.get_result())})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    elif company == 'zc':
        source_system = '资产'
        check = Check()
        if check.init_table(company, source_system, quarter):
            run = check.run_check(company, source_system, quarter, None)
            if run is not True:
                return JsonResponse({"status": "检核过程发生错误：" + str(run)})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    elif company == 'db':
        source_system = '担保'
        check = Check()
        if check.init_table(company, source_system, quarter):
            run = check.run_check(company, source_system, quarter, None)
            if run is not True:
                return JsonResponse({"status": "检核过程发生错误：" + str(run)})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    elif company == 'jk':
        source_system = '金科'
        check = Check()
        if check.init_table(company, source_system, quarter):
            run = check.run_check(company, source_system, quarter, None)
            if run is not True:
                return JsonResponse({"status": "检核过程发生错误：" + str(run)})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    elif company == 'jj1':
        source_system = '基金1'
        check = Check()
        if check.init_table(company, source_system, quarter):
            run = check.run_check(company, source_system, quarter, None)
            if run is not True:
                return JsonResponse({"status": "检核过程发生错误：" + str(run)})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    elif company == 'jj2':
        source_system = '基金2'
        check = Check()
        if check.init_table(company, source_system, quarter):
            run = check.run_check(company, source_system, quarter, None)
            if run is not True:
                return JsonResponse({"status": "检核过程发生错误：" + str(run)})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    elif company == 'jz':
        source_system = '金租'
        check = Check()
        if check.init_table(company, source_system, quarter):
            run = check.run_check(company, source_system, quarter, None)
            if run is not True:
                return JsonResponse({"status": "检核过程发生错误：" + str(run)})
        else:
            return JsonResponse({"status": "初始化检核表发生错误"})

    if run is True:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        curs.execute('set autocommit=0')
        sql = "insert into check_execute_log(status,company,execute_date,execute_user) values('success','{1}',now(),'{2}')".format(
            quarter, company, username)
        print(sql)
        curs.execute(sql)
        conn.commit()
        curs.close()
        conn.close()
        return JsonResponse({
            "status": "success",
            "msg": source_system + "公司检核成功！"
        })


def update_crontab(request):
    """更新crontab命令
    """
    job_time = request.POST.get('job_time')

    try:
        # cron  = CronTab(user=True)
        # job = list(cron.find_comment('自动进行数据质量检核'))[0]
        # job.setall(job_time)
        return JsonResponse({"msg": "操作成功"})
    except Exception as e:
        return JsonResponse({"msg": "操作失败", "reason": e})


@require_http_methods(['GET'])
def query_check_progress(request):
    """
    查询正在运行的检核任务执行进度
    :param request:
    :return:
    """
    data = {}
    try:
        conn = db_config.mysql_connect()
        # 获取所有配置的项目
        company_sql = "select menu_name_en from  hbd_menu where " \
                      "menu_name_en is not null and menu_name_en !='' and menu_validity=1 "
        with conn.cursor() as curs1:
            curs1.execute(company_sql)
            result_company = curs1.fetchall()
        for result in result_company:
            company = result[0]
            data[company] = 0
            with conn.cursor() as curs:
                # 已检核指标总数
                exe_sql = f"""
                    SELECT
                        a.company,
                        count(*) all_cnt,
                        sum(case 	update_flag 
                        when 'Y'  then 1
                        else 0 end ) y_cnt
                    FROM
                        check_result_{company} a,
                        (
                        SELECT
                            max( check_version ) check_version
                        FROM
                            check_result_{company} 
                        ) b 
                    WHERE 
                        a.check_version = b.check_version 
                        and a.status='已启用'
                    GROUP BY
                        a.company
                 """
                curs.execute(exe_sql)
                results = curs.fetchall()
                for item_result in results:
                    y_cnt = item_result[2]
                    all_cnt = item_result[1]
                    if all_cnt == 0:
                        continue
                    data[company] = round(y_cnt * 100 / all_cnt, 2)
        return JsonResponse(data)
    except Exception as e:
        return HttpResponseBadRequest(e)
    finally:
        conn.close()
